package net.aib.moneytracker.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {
	private static final String TAG = "DBHelper";
	
    private static final int DATABASE_VERSION = 3;
    private static final String DATABASE_NAME = "moneytracker";
    
	public static final String BUY_TABLE_NAME = "buy";
	public static final String BUY_KEY_AMOUNT = "amount";
	public static final String BUY_KEY_DATE = "date";
	private static final String BUY_TABLE_CREATE = 
		"CREATE TABLE " + BUY_TABLE_NAME 
		+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
		+ BUY_KEY_AMOUNT + " DOUBLE NOT NULL, "
		+ BUY_KEY_DATE + " DATETIME DEFAULT CURRENT_TIMESTAMP);";
	
	public static final String CATEGORY_TABLE_NAME = "category";
	public static final String CATEGORY_KEY_NAME = "name";
	private static final String CATEGORY_TABLE_CREATE = 
		"CREATE TABLE " + CATEGORY_TABLE_NAME
	 	+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
	 	+ CATEGORY_KEY_NAME + " TEXT NOT NULL);";
	
	public static final String COMPANY_TABLE_NAME = "company";
	public static final String COMPANY_KEY_NAME = "name";
	private static final String COMPANY_TABLE_CREATE = 
		"CREATE TABLE " + COMPANY_TABLE_NAME
		+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
		+ COMPANY_KEY_NAME + " TEXT NOT NULL);";

	public static final String BUY_KEY_ID = "buyid";
	public static final String COMPANY_KEY_ID = "companyid";
	public static final String CATEGORY_KEY_ID = "categoryid";
	
	public static final String BUYCOMPANY_TABLE_NAME = "buycompany";
	public static final String BUYCATEGORY_TABLE_NAME = "buycategory";
	public static final String COMPANYCATEGORY_TABLE_NAME = "companycategory";
	
	private static final String BUYCOMPANY_TABLE_CREATE = 
		"CREATE TABLE " + BUYCOMPANY_TABLE_NAME + " ("
		+ BUY_KEY_ID + " INTEGER, "
		+ COMPANY_KEY_ID + " INTEGER);";
	private static final String BUYCATEGORY_TABLE_CREATE =
		"CREATE TABLE " + BUYCATEGORY_TABLE_NAME + " ("
		+ BUY_KEY_ID + " INTEGER, "
		+ CATEGORY_KEY_ID + " INTEGER);";
	private static final String COMPANYCATEGORY_TABLE_CREATE = 
		"CREATE TABLE " + COMPANYCATEGORY_TABLE_NAME + " ("
		+ COMPANY_KEY_ID + " INTEGER, "
		+ CATEGORY_KEY_ID + " INTEGER);";
	
	public static final String ACCOUNT_KEY_CURRENT = "current";
	public static final String ACCOUNT_TABLE_NAME = "account";
	
	private static final String ACCOUNT_TABLE_CREATE = 
		"CREATE TABLE " + ACCOUNT_TABLE_NAME
		+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
		+ ACCOUNT_KEY_CURRENT + " DOUBLE NOT NULL);"; 
	
	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}	

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d(TAG, "Creating Tables");
		db.execSQL(ACCOUNT_TABLE_CREATE);
		db.execSQL(BUY_TABLE_CREATE);
		db.execSQL(COMPANY_TABLE_CREATE);
		db.execSQL(CATEGORY_TABLE_CREATE);
		db.execSQL(BUYCOMPANY_TABLE_CREATE);
		db.execSQL(BUYCATEGORY_TABLE_CREATE);
		db.execSQL(COMPANYCATEGORY_TABLE_CREATE);
		Log.d(TAG, "Creating Tables successful!");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "Upgrading database version! All data will be erased!");
        db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + BUY_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + COMPANY_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + CATEGORY_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + BUYCOMPANY_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + BUYCATEGORY_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + COMPANYCATEGORY_TABLE_NAME);
        onCreate(db);
	}
	
	public void clearDatabase(SQLiteDatabase db){
        Log.d(TAG, "Clearing the database! All data will be erased!");
        db.execSQL("DELETE FROM " + ACCOUNT_TABLE_NAME);
        db.execSQL("DELETE FROM " + BUY_TABLE_NAME);
        db.execSQL("DELETE FROM " + COMPANY_TABLE_NAME);
        db.execSQL("DELETE FROM " + CATEGORY_TABLE_NAME);
        db.execSQL("DELETE FROM " + BUYCOMPANY_TABLE_NAME);
        db.execSQL("DELETE FROM " + BUYCATEGORY_TABLE_NAME);
        db.execSQL("DELETE FROM " + COMPANYCATEGORY_TABLE_NAME);
	}
}
